package db.operation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class CRUD {
	/**
	 *防止查询时间过短，与输入定时时间对比 
	 */
	private double time = 0;
	private static int row=1;
	/**
	 *获取操作数据库的时间，与输入定时时间对比 ，防止操作不完整
	 */
	public double getTime() {
		setTime(time);
		return time;
	}

	public void setTime(double time) {
		this.time = time;
	}
	

	/**
	 *插值
	 *@param  insertSql
	 *@return"插值成功，影响的行数为：" + rowNum + "\n" + "用时" + time + "秒"
	 */
	public String insert(String insertSql) throws SQLException{
		String  result= "insert操作\n";
		Connection connection=null;
		PreparedStatement st=null;
		ResultSet rs=null;

		try{				
			connection=jdbcUtils.getConnection();
			st=connection.prepareStatement(insertSql);
			long insertStartTime = System.currentTimeMillis();
			int rowNum = st.executeUpdate();
			if(rowNum>0){
				time = (System.currentTimeMillis() - insertStartTime) / 1000.0;
				result = "插值成功，影响的行数为：" + rowNum + "\n" + "用时" + time + "秒";				
			}
		}finally{				
			jdbcUtils.release(connection, st, rs);					
		}	
		return result;
	}



	/**
	 *更新
	 *@param  updateSql
	 *@return"更新成功，影响的行数为：" + rowNum + "\n" + "用时：" + time + "秒";
	 */
	public String update(String updateSql) throws SQLException {
		String result="updata操作\n";
		Connection connection=null;
		PreparedStatement st=null;
		ResultSet rs=null;

		try{				
			connection=jdbcUtils.getConnection();
			st=connection.prepareStatement(updateSql);
			long insertStartTime = System.currentTimeMillis();
			int rowNum = st.executeUpdate();
			if(rowNum>0){
				time = (System.currentTimeMillis() - insertStartTime) / 1000.0;
				result= "更新成功，影响的行数为：" + rowNum + "\n" + "用时：" + time + "秒";
			}
		}finally{				
			jdbcUtils.release(connection, st, rs);				
		}		
		return result;
	}


	/**
	 *刪除 
	 *@param  deleteSql
	 *@return "刪除成功，影响的行数为：" + rowNum + "\n" + "用时：" + time + "秒";
	 */
	public String delete(String deleteSql) throws SQLException{
		String result = "delete操作\n";
		Connection connection=null;
		PreparedStatement st=null;
		ResultSet rs=null;			
		try{				
			connection=jdbcUtils.getConnection();
			st = connection.prepareStatement(deleteSql);
			long insertStartTime = System.currentTimeMillis();
			int rowNum = st.executeUpdate();
			if(rowNum>0)
			{
				time = (System.currentTimeMillis() - insertStartTime) / 1000.0;
				result = "刪除成功，影响的行数为：" + rowNum + "\n" + "用时：" + time + "秒";
			}
		}finally{				
			jdbcUtils.release(connection, st, rs);				
		}			
		return result;
	}



	
	
	/**
	 * 查找
	 * 1.preparedstatement是statement的子类；
	 * 2.preparedstatement可以防止sql注入;
	 * 3.preparedstatement可以对sql指令预编译，减轻服务器压力
	 * @param  deleteSql
	 * @return "条件筛选操作\n"+list+"\n"+ "返回记录：" + i + "条" + "\n" + "查询用时：" + time + "秒";
	 */
	public String find(String findSql) throws SQLException{
		long i=0;
		JSONArray records=new JSONArray();
		Connection connection=null;
		PreparedStatement st=null;
		ResultSet rs=null;

		try{	
			connection=jdbcUtils.getConnection();
			st=connection.prepareStatement(findSql);
			rs=st.executeQuery();
			long findStartTime = System.currentTimeMillis();
			while (rs.next()) {
				i++;
				JSONObject aRecord=new JSONObject();			
				aRecord.put("comment_num", rs.getString("comment_num"));
				aRecord.put("repost_num", rs.getString("repost_num"));
				aRecord.put("poster_id", rs.getString("poster_id"));
				aRecord.put("poster", rs.getString("poster"));
				aRecord.put("post_time", rs.getString("post_time"));
				aRecord.put("scratch_time", rs.getString("scratch_time"));
				aRecord.put("post_id", rs.getString("post_id"));
				//System.out.println(aRecord.toString());
				records.add(aRecord);
				//System.out.println(records.toString());
				
			}
			time = (System.currentTimeMillis() -findStartTime) / 1000.0;
		}finally{
			jdbcUtils.release(connection, st, rs);				
		}
		return records.toString()+"\n查询用时："+time+"秒，"+"查询结果数目："+i+"条";
	}

	
	/**
	 * 得到所有记录
	 * @param  null
	 * @return "所有数据\n"+list+"\n"+ "返回记录：" + i + "条" + "\n" + "查询用时：" + time + "秒";
	 */
//	public String getAll() throws SQLException{
//		String result ="";
//		long i=0;
//		java.util.List<String> list=new ArrayList<String>();
//		Connection connection=null;
//		Statement st=null;
//		ResultSet rs=null;
//		try{				
//			connection=jdbcUtils.getConnection();
//			st=connection.createStatement();
//			String sql="select * from copy_post";
//			rs=st.executeQuery(sql);		
//			long getAllStartTime = System.currentTimeMillis();
//			while (rs.next()) {
//				i++;
//				result="{"+
//						"\""+"poster"     +"\""+":"+rs.getString("poster")      +","+
//						"\""+"poster_id"  +"\""+":"+rs.getString("poster_id")   +","+
//						"\""+"post_time"  +"\""+":"+rs.getString("post_time")   +","+
//						"\""+"post_id"    +"\""+":"+rs.getString("post_id")     +","+
//						"\""+"comment_num"+"\""+":"+rs.getString("comment_num") +","+
//						"\""+"repost_num" +"\""+":"+rs.getString("repost_num")  +","+
//						"\""+"scratch_time" +"\""+":"+rs.getString("scratch_time")  +
//						"}";
//				list.add(result);			
//			}		
//			time = (System.currentTimeMillis() - getAllStartTime) / 1000.0;
//		}finally{				
//			jdbcUtils.release(connection, st, rs);				
//		}		
//		result="所有数据\n"+list+"\n"+ "返回记录：" + i + "条" + "\n" + "查询用时：" + time + "秒";
//		return result;
//	}

/**
 *得到所有合同
 */
public JSONArray getAllContracts(String sql) throws SQLException {
	//long i=0;
	
	//JSONObject usersData=new JSONObject();
	JSONArray records=new JSONArray();
	JSONObject aRecord=new JSONObject();			
	Connection connection=null;
	Statement st=null;
	ResultSet rs=null;
	try{	
		connection=jdbcUtils.getConnection();
		st=connection.createStatement();
		
		rs=st.executeQuery(sql);		
		long getAllStartTime = System.currentTimeMillis();
		while(rs.next()) {	
				
			aRecord.put("title", rs.getString("title"));
			aRecord.put("sign_time", rs.getString("sign_time"));
			aRecord.put("content", rs.getString("content"));
			aRecord.put("contractstyle", rs.getObject("contractstyle"));
			records.add(aRecord);
		}	
		time = (System.currentTimeMillis() - getAllStartTime) / 1000.0;
	}finally{				
		jdbcUtils.release(connection, st, rs);				
	}		
	return 	records;			

}	

/**
 *得到预约历史
 */
public JSONArray getReserveHistory(String sql) throws SQLException {
	//long i=0;
	
	//JSONObject usersData=new JSONObject();
	JSONArray records=new JSONArray();
	JSONObject aRecord=new JSONObject();			
	Connection connection=null;
	Statement st=null;
	ResultSet rs=null;
	try{	
		connection=jdbcUtils.getConnection();
		st=connection.createStatement();
		
		rs=st.executeQuery(sql);		
		long getAllStartTime = System.currentTimeMillis();
		while(rs.next()) {	
				
			aRecord.put("title", rs.getString("title"));
			aRecord.put("res_time", rs.getString("res_time"));
			aRecord.put("remark", rs.getString("remark"));
			aRecord.put("status", rs.getObject("status"));
			records.add(aRecord);
		}	
		time = (System.currentTimeMillis() - getAllStartTime) / 1000.0;
	}finally{				
		jdbcUtils.release(connection, st, rs);				
	}		
	return 	records;			

}	
}

	
	
	